<?php
class xl_product{
    function insert($data){
        global $dbh;
        $sql='INSERT INTO product(category_id,brand_products_id,name,alias,price_old,price_new,material,size,color,warranty,quantity,title,keyword,description,image,highlights,status,info,day_time) 
            VALUE(:category_id,:brand_products_id,:name,:alias,:price_old,:price_new,:material,:size,:color,:warranty,:quantity,:title,:keyword,:description,:image,:highlights,:status,:info,:day_time)';    
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    function insert_list($product_id,$image){
        global $dbh;
        $sql='INSERT INTO product_images(product_id,image) VALUE (:product_id,:image)';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':product_id'=>$product_id,':image'=>$image));
        return $sth;
    }
    function read_product_image(){
        global $dbh;
        $sql='SELECT * FROM product ORDER BY id DESC LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function read(){
        global $dbh;
        $sql='SELECT product.*, brand.image AS image_brand,brand.day_time AS brand_day_time FROM `product` left join brand_products as brand on product.brand_products_id=brand.id ORDER BY id DESC';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
    function read_brand(){
        global $dbh;
        $sql='SELECT * FROM brand_products';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
    function read_category(){
        global $dbh;
        $sql='SELECT * FROM product_categories';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
    function read_id($id){
        global $dbh;
        $sql='SELECT * FROM product WHERE id=:id LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function read_id_brand($id){
        global $dbh;
        $sql='SELECT id FROM brand_products WHERE id=:id LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function read_id_category($id){
        global $dbh;
        $sql='SELECT * FROM product_categories WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function update_status($id){
        global $dbh;
        $sql='UPDATE product SET status=1-status WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function update($data){
        global $dbh;
        if($data['image']!=''){
            $sql='UPDATE product SET category_id=:category_id,brand_products_id=:brand_products_id,name=:name,price_old=:price_old,price_new=:price_new,material=:material,size=:size,color=:color,warranty=:warranty,quantity=:quantity,title=:title,keyword=:keyword,description=:description,image=:image,highlights=:highlights,status=:status,info=:info,day_time=:day_time WHERE id=:id';
        } else {
            $sql='UPDATE product SET category_id=:category_id,brand_products_id=:brand_products_id,name=:name,price_old=:price_old,price_new=:price_new,material=:material,size=:size,color=:color,warranty=:warranty,quantity=:quantity,title=:title,keyword=:keyword,description=:description,highlights=:highlights,status=:status,info=:info,day_time=:day_time WHERE id=:id';
            unset($data['image']);
        }
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    function update_product_image($data1){
        global $dbh;
        if($data1['image']!=''){
            $sql='UPDATE product_images SET image=:image WHERE product_id=:product_id';
        } else {
            $sql="UPDATE product_images SET WHERE product_id=:product_id";
            unset($data1['image']);
        }
        $sth=$dbh->prepare($sql);
        $sth->execute($data1);
        return $sth;
    }
    function delete($id){
        global $dbh;
        $sql='DELETE FROM product WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
    }
    function delete_all($id){
        global $dbh;
        $sql='SELECT * FROM product WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function delete_product_images($product_id){
        global $dbh;
        $sql='DELETE FROM product_images WHERE product_id=:product_id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':product_id'=>$product_id));
        return $sth;
    }
    function delete_all_product_images($product_id){
        global $dbh;
        $sql='SELECT * FROM product_images WHERE product_id=:product_id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':product_id'=>$product_id));
        return $sth->fetchAll();
    }
    function test($id,$name){
        global $dbh;
        $sql='SELECT * FROM product WHERE id!=:id AND name=:name';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id,':name'=>$name));
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }
    function test_name($alias){
        global $dbh;
        $sql='SELECT alias FROM product WHERE alias=:alias';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>$alias));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    
}